const db = require("../config/db");

class User {
  // 分页查询用户
  static async searchUsers({ page = 1, pageSize = 10, username, email }) {
    const offset = (page - 1) * pageSize;
    // 构建查询条件
    let query = "SELECT * FROM users";
    const params = [];
    if (username) {
      query += " WHERE username LIKE ?";
      params.push(`%${username}%`);
    }
    if (email) {
      if (username) {
        query += " OR email LIKE ?";
      } else {
        query += " WHERE email LIKE ?";
      }
      params.push(`%${email}%`);
    }
    query += " LIMIT ? OFFSET ?";
    params.push(pageSize, offset);
    // 查询分页数据
    const [rows] = await db.query(query, params);
    // 构建总记录数查询
    let countQuery = "SELECT COUNT(*) AS total FROM users";
    const countParams = [];
    if (username) {
      countQuery += " WHERE username LIKE ?";
      countParams.push(`%${username}%`);
    }
    if (email) {
      if (username) {
        countQuery += " OR email LIKE ?";
      } else {
        countQuery += " WHERE email LIKE ?";
      }
      countParams.push(`%${email}%`);
    }
    // 查询总记录数
    const [countResult] = await db.query(countQuery, countParams);
    const total = countResult[0].total;
    return { data: rows, total, page, pageSize };
  }
  static async findAll() {
    const [rows] = await db.query("SELECT * FROM users");
    console.log(rows);
    return rows;
  }

  static async findById(id) {
    const [rows] = await db.query("SELECT * FROM users WHERE id = ?", [id]);
    console.log(rows);
    return rows.length ? rows[0] : null;
  }

  static async findByEmail(email) {
    const [rows] = await db.query("SELECT * FROM users WHERE email = ?", [
      email,
    ]);
    console.log(rows);
    return rows.length ? rows[0] : null;
  }

  static async create(user) {
    // 确保必填字段存在
    if (!user.username || !user.email || !user.password) {
      throw new Error("缺少必要用户信息");
    }
    const [result] = await db.query(
      "INSERT INTO users (username, email, password, created_at, updated_at) VALUES (?, ?, ?, NOW(), NOW())",
      [user.username, user.email, user.password]
    );
    return result.insertId;
  }

  static async update(id, updateData) {
    // 构建动态更新语句
    const fields = [];
    const params = [];
    if (updateData.username !== undefined) {
      fields.push("username = ?");
      params.push(updateData.username);
    }
    if (updateData.email !== undefined) {
      fields.push("email = ?");
      params.push(updateData.email);
    }
    if (updateData.password !== undefined) {
      fields.push("password = ?");
      params.push(updateData.password);
    }
    fields.push("updated_at = NOW()");
    // 如果没有需要更新的字段
    if (fields.length === 0) {
      return false;
    }
    const query = `UPDATE users SET ${fields.join(", ")} WHERE id = ?`;
    params.push(id);
    const [result] = await db.query(query, params);
    return result.affectedRows > 0;
  }

  static async delete(id) {
    const [result] = await db.query("DELETE FROM users WHERE id = ?", [id]);
    console.log(result);
    return result.affectedRows > 0;
  }

  // 批量删除用户
  static async deleteBatch(ids) {
    if (!Array.isArray(ids)) {
      throw new Error("参数必须是数组");
    }
    // 过滤有效数字ID
    const validIds = ids.map((id) => parseInt(id)).filter((id) => !isNaN(id));
    if (validIds.length === 0) {
      return 0;
    }
    // 生成安全的SQL查询
    const placeholders = validIds.map(() => "?").join(",");
    const query = `DELETE FROM users WHERE id IN (${placeholders})`;

    const [result] = await db.query(query, validIds);
    return result.affectedRows;
  }
}

module.exports = User;
